package com.wuliu.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.wuliu.model.common.PageVO;
import com.wuliu.model.common.QueryVO;
import com.wuliu.util.BaseUtil;
import com.wuliu.util.DbPool;

public class DbUtil {
	public static Log log = LogFactory.getLog("DbUtil"); 
	public static int pageCount = 15;
	public static int pageCount50 = 15;
	public static int pageCount100 = 15;
	
	/**
	 * @Title: execute
	 * @Description: 批量执行SQL
	 * @return boolean 执行是否成功
	 */
	public static final boolean execute(String sql, List<Object[]> par) {
		if (sql == null || par.isEmpty()) {
			return false;
		}
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbPool.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			// 开始批量执行
			int count = 0;
			for (Object[] ele : par) {
				for (int i = 0; i < ele.length; i++) {
					if (ele[i] instanceof String) {
						ps.setString(i + 1, (String) ele[i]);
					} else if (ele[i] instanceof Number) {
						ps.setLong(i + 1, ((Number) ele[i]).longValue());
					} else {
						ps.setObject(i + 1, ele[i]);
					}
				}
				ps.addBatch();
				if (++count >= 100) {
					ps.executeBatch();
					count = 0;
				}
			}
			ps.executeBatch();
			conn.commit();
			return true;
		} catch (Exception e) {
			log.error("Execute SQL error:" + sql, e);
			return false;
		} finally {
			DbPool.close(conn, ps, null);
		}
	}

	/**
	 * @Title: execute
	 * @Description: 批量执行SQL
	 * @return boolean 执行是否成功
	 */
	public static final boolean execute(List<String> sqls) {
		if (sqls == null || sqls.isEmpty()) {
			return false;
		}
		return execute(sqls.toArray(new String[sqls.size()]));
	}
	
	/**
	 * @Title: execute
	 * @Description: 批量执行SQL
	 * @param 要执行的sql集合
	 * @return boolean 执行是否
	 * @throws
	 */
	public static final boolean execute(String... sqls) {
		if (sqls == null || sqls.length == 0) {
			return false;
		}
		Statement ps = null;
		Connection conn = null;
		String sql = null;
		try {
			conn = DbPool.getConnection();
			conn.setAutoCommit(false);
			ps = conn.createStatement();
			// 开始批量执行
			int count = 0;
			for (int i = 0; i < sqls.length; i++) {
				sql = sqls[i];
				log.info("execute sql:" + sql);
				if (sql != null) {
					ps.execute(sql);
					if (++count >= 100) {
						conn.commit();
						count = 0;
					}
				}
			}
			conn.commit();
			return true;
		} catch (Exception e) {
			log.error("Execute SQL error:" + sql, e);
			return false;
		} finally {
			DbPool.close(conn, ps, null);
		}
	}
	/**
	 * @Title: excu
	 * @Description: 执行SQL
	 * @param 要执行的sql
	 * @return boolean 执行是否
	 * @throws
	 */
	public static final boolean excu(String sql) {
		if (sql == null || sql.equals("")) {
			return false;
		}
		Statement ps = null;
		Connection conn = null;
		try {
			conn = DbPool.getConnection();
			ps = conn.createStatement();
			log.info("execute sql:" + sql);
			ps.execute(sql);
			return true;
		} catch (Exception e) {
			log.error("Execute SQL error:" + sql, e);
			return false;
		} finally {
			DbPool.close(conn, ps, null);
		}
	}

    public static final boolean excuSQL(List<String> list, String sql) {
        PreparedStatement stmt = null;
        Connection con = null;
        try {
            if (con == null) {
                con = DbPool.getConnection();
            }
            stmt = con.prepareStatement(sql);
            for (int bi = 0; bi < list.size(); bi++) {
                if (list.get(bi) == null)
                    stmt.setObject(bi + 1, "");
                else
                    stmt.setObject(bi + 1, list.get(bi));
            }
            stmt.executeUpdate();
            return true;
        } catch (Exception e) {
			log.error("Execute SQL error:" + sql, e);
			return false;
		} finally {
			DbPool.close(con, stmt, null);
		}
    }
    public static final boolean excuSQL2(List<Object> list, String sql) {
        PreparedStatement stmt = null;
        Connection con = null;
        try {
            if (con == null) {
                con = DbPool.getConnection();
            }
            stmt = con.prepareStatement(sql);
            for (int bi = 0; bi < list.size(); bi++) {
                if (list.get(bi) == null)
                    stmt.setObject(bi + 1, "");
                else
                    stmt.setObject(bi + 1, list.get(bi));
            }
            stmt.executeUpdate();
            return true;
        } catch (Exception e) {
			log.error("Execute SQL error:" + sql, e);
			return false;
		} finally {
			DbPool.close(con, stmt, null);
		}
    }
    
    public static final String excuSQL1(List<String> list, String sql) {
        PreparedStatement stmt = null;
        Connection con = null;
        try {
            if (con == null) {
                con = DbPool.getConnection();
            }
            stmt = con.prepareStatement(sql);
            for (int bi = 0; bi < list.size(); bi++) {
                if (list.get(bi) == null)
                    stmt.setObject(bi + 1, "");
                else
                    stmt.setObject(bi + 1, list.get(bi));
            }
            stmt.executeUpdate();
            return "ok";
        } catch (Exception e) {
			log.error("Execute SQL error:" + sql, e);
			return e.toString();
		} finally {
			DbPool.close(con, stmt, null);
		}
    }
    public static String getQueryFiledValue(String sql, String filed) {
        Connection con = null; 
        PreparedStatement stmt = null; 
        ResultSet dest = null; 
        String filedValue = "";
        try {
            if (con == null) {
                con = DbPool.getConnection(); 
            } 
            stmt = con.prepareStatement(sql.toString());
            dest = stmt.executeQuery();
            if(dest.next()) {
            	filedValue = BaseUtil.getStr(dest.getString(filed));
            }
        } catch (Exception e) {
			log.error("Execute SQL error:" + sql, e);
		} finally {
			DbPool.close(con, stmt, null);
		}

        return filedValue;
    }
    
    
 // 拼接sql 查询语句3 比较前一个添加了条件HashMap
    public static final List<List<String>> queryStrForPage(List<?> list, String tableName,
            String intCols, HashMap<?, ?> hm, String orderbyStr, int pageNum) {
        Connection con = null;
        PreparedStatement stmt = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        ResultSet dest = null;
        for (int i = 0; i < list.size(); i++) {
            QueryVO qVo = (QueryVO) list.get(i);
            if(i==0){
                sql.append(qVo.getCols());
            }else{
                sql.append("," + qVo.getCols());
            }

        }
        sql.append(" FROM  " + tableName + " ");
        sql.append(" " + orderbyStr);
        sql.append(" limit " + ((pageNum-1) * DbUtil.pageCount)+ " , " + pageCount + " ");
     
        System.out.println("query sql:"+sql);
        
        List<List<String>> lt = new ArrayList<List<String>>();
        try {
            if (con == null) {
                con = DbPool.getConnection(); 
            }

            stmt = con.prepareStatement(sql.toString());
            log.info("列表查询："+sql.toString());
            dest = stmt.executeQuery();
            while (dest.next()) {
                List<String> ltDetail = new ArrayList<String>();
                for (int i = 1; i <= list.size(); i++) {
                    if (intCols.indexOf(String.valueOf(i - 1 + 1000)) >= 0){// 需要特殊显示的标记;//在原来基础上加1000，方便处理特殊显示  
                        ltDetail.add(" <input type='"+ ((QueryVO) list.get(i - 1)).getTagHtml()+ "' "
                                + BaseUtil.getStr(((QueryVO) list.get(i - 1)).getTagOther()) 
                                + " name='"+ ((QueryVO) list.get(i - 1)).getTagName()
                                + "' value='" + BaseUtil.getStr(dest.getString(i))
                                + "' >");
                    } else{ 
                        ltDetail.add(BaseUtil.getStr(dest.getString(i)));
                    }
                }
                lt.add(ltDetail);
            }
        } catch (Exception ee) { 
        	log.error("Execute SQL error:" + sql, ee);
            System.out.println(ee + "，，queryStr=" + sql);
        } finally { 
        	DbPool.close(con, stmt, null);
        }

        return lt;
    }
    
    // next_num 当前的 要显示的页号
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public static final PageVO pageObjFor(List<?> list, String next_num,
            int queryCount) {
        int first_page = 0, last_page = 0, previous_page = 0, next_page = 0, present_page = 0, all_page = 0, all_size = 0;
        String first_disable = "", last_disable = "", previous_disable = "", next_disable = "";
        List lt = new ArrayList();
        present_page = Integer.parseInt(next_num);// 当前的 要显示的页号
        if (list != null) {
            all_size = queryCount;
            all_page = queryCount / DbUtil.pageCount;
            if (queryCount % DbUtil.pageCount > 0)
                all_page = all_page + 1;
            if (queryCount > DbUtil.pageCount) {

                int num = 0;
                for (int i = 0; i < list.size(); i++) {
                    num++;
                    lt.add(list.get(i));
                    if (num >= DbUtil.pageCount)
                        break;
                }
                first_page = 1;
                last_page = all_page;
                if (last_page > present_page) {
                    last_disable = "";
                    next_disable = "";
                    next_page = present_page + 1;
                } else {
                    last_disable = "disabled";
                    next_disable = "disabled";
                }
                if (present_page > 1) {
                    previous_page = present_page - 1;
                    previous_disable = "";
                    first_disable = "";
                } else {
                    previous_disable = "disabled";
                    first_disable = "disabled";
                }
            } else {
                last_disable = "disabled";
                next_disable = "disabled";
                previous_disable = "disabled";
                first_disable = "disabled";
                lt = list;
            }
        }
        PageVO vo = new PageVO();
        vo.setAll_size(all_size);
        vo.setFirst_page(first_page);
        vo.setLast_page(last_page);
        vo.setPrevious_page(previous_page);
        vo.setNext_page(next_page);
        vo.setPresent_page(present_page);
        vo.setAll_page(all_page);
        vo.setFirst_disable(first_disable);
        vo.setLast_disable(last_disable);
        vo.setPrevious_disable(previous_disable);
        vo.setNext_disable(next_disable);
        vo.setList(lt);

        int preNum = present_page-3;
        System.out.println("present_page------|  "+present_page);
        System.out.println("preNum------------|  "+preNum);
        int nextNum = present_page+3;
        System.out.println("nextNum-----------|  "+nextNum);
        if(preNum<=0){
        	vo.setFirst_pre_page(1);
        	if(nextNum>last_page){
        		vo.setLast_pre_page(last_page);
        	}else{
        		if(last_page>6){
        			vo.setLast_pre_page(6);
        		}else{
        			vo.setLast_pre_page(nextNum);
        		}
        	}
        }else{
        	vo.setFirst_pre_page(preNum);
        	if(nextNum>last_page){
        		vo.setLast_pre_page(last_page);
        	}else{
        		vo.setLast_pre_page(nextNum);
        	}
        }
        	
        
        return vo;
    }
    
    // next_num 当前的 要显示的页号
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public static final PageVO pageObj(List<?> list, String next_num,
            int queryCount) {
        int first_page = 0, last_page = 0, previous_page = 0, next_page = 0, present_page = 0, all_page = 0, all_size = 0;
        String first_disable = "", last_disable = "", previous_disable = "", next_disable = "";
        List lt = new ArrayList();
        present_page = Integer.parseInt(next_num);// 当前的 要显示的页号
        if (list != null) {
            all_size = queryCount;
            all_page = queryCount / DbUtil.pageCount;
            if (queryCount % DbUtil.pageCount > 0)
                all_page = all_page + 1;
            if (queryCount > DbUtil.pageCount) {

            	int current = (present_page - 1) * DbUtil.pageCount + 1;
                int num = 0;
                for (int i = current-1; i < list.size(); i++) {
                    num++;
                    lt.add(list.get(i));
                    if (num >= DbUtil.pageCount)
                        break;
                }
                first_page = 1;
                last_page = all_page;
                if (last_page > present_page) {
                    last_disable = "";
                    next_disable = "";
                    next_page = present_page + 1;
                } else {
                    last_disable = "disabled";
                    next_disable = "disabled";
                }
                if (present_page > 1) {
                    previous_page = present_page - 1;
                    previous_disable = "";
                    first_disable = "";
                } else {
                    previous_disable = "disabled";
                    first_disable = "disabled";
                }
            } else {
                last_disable = "disabled";
                next_disable = "disabled";
                previous_disable = "disabled";
                first_disable = "disabled";
                lt = list;
            }
        }
        PageVO vo = new PageVO();
        vo.setAll_size(all_size);
        vo.setFirst_page(first_page);
        vo.setLast_page(last_page);
        vo.setPrevious_page(previous_page);
        vo.setNext_page(next_page);
        vo.setPresent_page(present_page);
        vo.setAll_page(all_page);
        vo.setFirst_disable(first_disable);
        vo.setLast_disable(last_disable);
        vo.setPrevious_disable(previous_disable);
        vo.setNext_disable(next_disable);
        vo.setList(lt);

        int preNum = present_page-3;
        System.out.println("present_page------|  "+present_page);
        System.out.println("preNum------------|  "+preNum);
        int nextNum = present_page+3;
        System.out.println("nextNum-----------|  "+nextNum);
        if(preNum<=0){
        	vo.setFirst_pre_page(1);
        	if(nextNum>last_page){
        		vo.setLast_pre_page(last_page);
        	}else{
        		if(last_page>6){
        			vo.setLast_pre_page(6);
        		}else{
        			vo.setLast_pre_page(nextNum);
        		}
        	}
        }else{
        	vo.setFirst_pre_page(preNum);
        	if(nextNum>last_page){
        		vo.setLast_pre_page(last_page);
        	}else{
        		vo.setLast_pre_page(nextNum);
        	}
        }
        	
        
        return vo;
    }
    
    // next_num 当前的 要显示的页号	每页显示的数量有传入参数决定 pageCount-每页的显示数量
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public static final PageVO pageObjForInput(List<?> list, String next_num,
            int queryCount,int pageCount) {
        int first_page = 0, last_page = 0, previous_page = 0, next_page = 0, present_page = 0, all_page = 0, all_size = 0;
        String first_disable = "", last_disable = "", previous_disable = "", next_disable = "";
        List lt = new ArrayList();
        present_page = Integer.parseInt(next_num);// 当前的 要显示的页号
        if (list != null) {
            all_size = queryCount;
            all_page = queryCount / pageCount;
            if (queryCount % pageCount > 0)
                all_page = all_page + 1;
            if (queryCount > pageCount) {

                int num = 0;
                for (int i = 0; i < list.size(); i++) {
                    num++;
                    lt.add(list.get(i));
                    if (num >= pageCount)
                        break;
                }
                first_page = 1;
                last_page = all_page;
                if (last_page > present_page) {
                    last_disable = "";
                    next_disable = "";
                    next_page = present_page + 1;
                } else {
                    last_disable = "disabled";
                    next_disable = "disabled";
                }
                if (present_page > 1) {
                    previous_page = present_page - 1;
                    previous_disable = "";
                    first_disable = "";
                } else {
                    previous_disable = "disabled";
                    first_disable = "disabled";
                }
            } else {
                last_disable = "disabled";
                next_disable = "disabled";
                previous_disable = "disabled";
                first_disable = "disabled";
                lt = list;
            }
        }
        PageVO vo = new PageVO();
        vo.setAll_size(all_size);
        vo.setFirst_page(first_page);
        vo.setLast_page(last_page);
        vo.setPrevious_page(previous_page);
        vo.setNext_page(next_page);
        vo.setPresent_page(present_page);
        vo.setAll_page(all_page);
        vo.setFirst_disable(first_disable);
        vo.setLast_disable(last_disable);
        vo.setPrevious_disable(previous_disable);
        vo.setNext_disable(next_disable);
        vo.setList(lt);

        int preNum = present_page-3;
        System.out.println("present_page------|  "+present_page);
        System.out.println("preNum------------|  "+preNum);
        int nextNum = present_page+3;
        System.out.println("nextNum-----------|  "+nextNum);
        if(preNum<=0){
        	vo.setFirst_pre_page(1);
        	if(nextNum>last_page){
        		vo.setLast_pre_page(last_page);
        	}else{
        		if(last_page>6){
        			vo.setLast_pre_page(6);
        		}else{
        			vo.setLast_pre_page(nextNum);
        		}
        	}
        }else{
        	vo.setFirst_pre_page(preNum);
        	if(nextNum>last_page){
        		vo.setLast_pre_page(last_page);
        	}else{
        		vo.setLast_pre_page(nextNum);
        	}
        }
        	
        
        return vo;
    }
    
    // 拼接sql 查询语句3 比较前一个添加了条件HashMap
    public static final int queryStrCount(List<?> list, String tableName,
            String intCols, HashMap<?, ?> hm, String orderbyStr) {
        Connection con = null;
        PreparedStatement stmt = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT COUNT(*) AS countNum ");
        ResultSet dest = null;
        int pageQueryNum = 0;
        sql.append(" FROM  " + tableName + " ");
        try {
            if (con == null) {
                con = DbPool.getConnection(); 
            }

            stmt = con.prepareStatement(sql.toString());
            dest = stmt.executeQuery(); 
            while (dest.next()) {
                pageQueryNum = dest.getInt("countNum"); 
            }
        } catch (Exception ee) {
        	log.error("Execute SQL error:" + sql, ee);
            System.out.println(ee + "，，queryStr=" + sql);
        } finally { 
        	DbPool.close(con, stmt, null);
        }

        return pageQueryNum;
    }
     
    
    public static final int exeCount(String sql,Object[] par) {
        Connection con = null;
        PreparedStatement stmt = null; 
        ResultSet dest = null;
        int count = 0;
        try {
            if (con == null) {
                con = DbPool.getConnection(); 
            }
            stmt = con.prepareStatement(sql.toString());
            int i=0;
            for (Object ele : par) {  
				if (ele instanceof String) {
					stmt.setString(i + 1, (String) ele);
				} else if (ele instanceof Number) {
					stmt.setLong(i + 1, ((Number) ele).longValue());
				} else {
					stmt.setObject(i + 1, ele);
				}
				i++;
            } 
            dest = stmt.executeQuery(); 
            while (dest.next()) {
            	count = dest.getInt(1); 
            }
        } catch (Exception ee) {
        	count = -1 ;
        	log.error("Execute SQL error:" + sql, ee);
            System.out.println(ee + "，，queryStr=" + sql);
        } finally { 
        	DbPool.close(con, stmt, null);
        }

        return count;
    }
    
    public static final List<List<String>> getQueryList(String sql, int count) {
        Connection con = null;
        ResultSet dest = null;
        List<List<String>> lt = new ArrayList<List<String>>();
        PreparedStatement stmt = null;
        try {
            if (con == null) {
                con = DbPool.getConnection(); 
            } 
            stmt = con.prepareStatement(sql);
            dest = stmt.executeQuery();
            while (dest.next()) {
                List<String> list = new ArrayList<String>();
                for (int i = 1; i <= count; i++) {

                    list.add(BaseUtil.getStr(dest.getString(i)));
                }
                lt.add(list);
            }
        } catch (Exception ee) {
            log.info(sql);
            log.info("   ;异常=" + ee);
            System.out.println(ee + "，，getSqlStr");
        } finally {
        	DbPool.close(con, stmt, null);
        } 
        return lt;
    }  

 // 执行编辑语句
    public static final List<?> getListVO(String sql, int count) {
        Connection con = null;
        ResultSet dest = null;
        List<List<String>> list = new ArrayList<List<String>>();
        PreparedStatement stmt = null;
        try { // 连接数据库
            if (con == null) {
                con = DbPool.getConnection();
            }

            stmt = con.prepareStatement(sql);

            dest = stmt.executeQuery();

            while (dest.next()) {
                List<String> lt = new ArrayList<String>();
                for (int i = 1; i <= count; i++) {

                    lt.add(BaseUtil.getStr(dest.getString(i)));
                }
                list.add(lt);

            }
        } catch (Exception ee) {
            log.info(sql);
            log.info("   ;异常=" + ee);
            System.out.println(ee + "，，getListVO");
        } finally {
        	DbPool.close(con, stmt, dest);
        }

        return list;
    }
}
